import pymysql.cursors


class SqlDemo(object):

    def __init__(self,ip,user,pwd,db):
        self.conn = pymysql.connect(host=ip,
                       user=user,
                       password=pwd,
                       database=db,
                       charset='utf8',
                       cursorclass=pymysql.cursors.DictCursor)
        # self.cursor = self.conn.cursor()


    def __del__(self):
        # self.cursor.close()
        self.conn.close()

    def createTable(self):
        cursor = self.conn.cursor()
        with cursor:
            # 定义要执行的SQL语句
            sql = """
            CREATE TABLE userinfo (
            id INT auto_increment PRIMARY KEY ,
            user varchar(100) NOT NULL,
            pwd varchar(100) NOT NULL
            )ENGINE=innodb DEFAULT CHARSET=utf8;  # 注意：charset='utf8' 不能写成utf-8
            """

            # 执行SQL语句
            self.cursor.execute(sql)


    def insert(self):
        cursor = self.conn.cursor()
        # 自动的关闭cursor
        with cursor:
            # 定义要执行的sql语句
            sql = 'insert into userinfo(user,pwd) values(%s,%s);'
            data = [
                ('july', '147'),
                ('june', '258'),
                ('marin', '369')
            ]
            # 拼接并执行sql语句
            cursor.executemany(sql, data)

            # 涉及写操作要注意提交
            self.conn.commit()

    def insertOne(self):
        cursor = self.conn.cursor()
        # 自动的关闭cursor
        with cursor:
            # 定义要执行的sql语句
            sql = 'insert into userinfo(user,pwd) values(%s,%s);'
            name = 'wuli'
            pwd = '123456789'
            cursor.execute(sql, [name, pwd])
            self.conn.commit()

    def checkLogin(self):
        # 执行sql语句
        user = 'root'
        pwd = '123456'
        sql = 'select * from userinfo where user = "%s" and pwd="%s"' % (user, pwd)
        print(sql)
        res = self.cursor.execute(sql)
        print(res)

        # 进行判断
        if res:
            print('登录成功')
        else:
            print('登录失败')

if __name__ == '__main__':
    d = SqlDemo('localhost','root','123456','test')
    # d.createTable()
    # d.insert()
    d.insertOne()
    pass
